Cviko 1
------------------------
SELECT * FROM sk.student

SELECT * FROM sk.student
 ORDER BY priezvisko
          meno

SELECT * FROM sk.student
 ORDER BY 2
          1

SELECT * FROM sk.student
 ORDER BY 2 DESC
          1

SELECT * FROM sk.student
 ORDER BY 1 ASC,2 DESC

SELECT * FROM sk.student
 ORDER BY 1, priezvisko DESC

SELECT meno FROM sk.student
 ORDER BY 1

SELECT priezvisko FROM sk.student
 ORDER BY 1

SELECT priezvisko FROM sk.student
 ORDER BY meno

SELECT priezvisko, meno FROM sk.student
 ORDER BY meno

SELECT priezvisko, meno FROM sk.student
 ORDER BY 2, 1 DESC

SELECT priezvisko || meno FROM sk.student - spoji meno s priezviskom
 ORDER BY 1 DESC   

SELECT priezvisko ||' '|| meno FROM sk.student - ako predosle, ale s medzerou
 ORDER BY 1 DESC

SELECT priezvisko ||' '|| meno AS student FROM sk.student - nazve stlpec AS student, AS je nepovinne, podobne ako ASC v predoslych prikladoch
 ORDER BY student 
- to, co je za AS sa vola alias

SELECT priezvisko ||','|| meno AS student FROM sk.student - namiesto medzery je ','
 ORDER BY student 

SELECT priezvisko ||' '|| substr(meno, zac, kolko) || '.' AS student FROM sk.student
 ORDER BY student 

SELECT substr(meno, 1, 1) || '.'  ||' '|| priezvisko AS student FROM sk.student
 ORDER BY student 

SELECT meno ||' '|| substr(priezvisko, 1, 1) || '.' AS zlocinec FROM sk.student
 ORDER BY zlocinec 

SELECT meno ||' '|| meno || 'ovic' ||' '|| priezvisko ||'vic' AS zlocinec FROM sk.student
 ORDER BY zlocinec 

SELECT * FROM sk.student WHERE meno!='Jan' - alebo <> namiesto !=
 ORDER BY 1

SELECT * FROM sk.student WHERE NOT(meno='Jan')
 ORDER BY 1

SELECT * FROM sk.student WHERE SUBSTR(priezvisko,1,1)='B' --- vypise len, tych ktorych priezvisko zacina na B
 ORDER BY 1

SELECT * FROM sk.student WHERE SUBSTR(priezvisko,LENGTH(priezvisko),1)='a'
 ORDER BY 1

SELECT * FROM sk.student WHERE priezvisko LIKE '%o%' --- pripadne pouzitie na predchadzajuce ulohy '%a', 'B%'
 ORDER BY 1

SELECT * FROM sk.student WHERE priezvisko LIKE '%o%' AND meno!='Jan' --- pre spojenie podmienok AND, OR
 ORDER BY 1

-------------------------------
CVIKO 2
-------------------------------
SELECT COUNT(*) FROM sk.student - pocet zaznamov

SELECT COUNT(meno) FROM sk.student WHERE meno='Jan'

SELECT DISTINCT meno FROM sk.student -- vypise rozne mena

SELECT COUNT(DISTINCT meno) FROM sk.student -- POCET roznych mien

vytvorenie tabulky --------------
CREATE TABLE ps.student
 (
   meno  VARCHAR(10),
   priezvisko  VARCHAR(20)
 )
-- naplnenie udajov do tabulky
INSERT INTO ps.student
 VALUES
 ('Jan','Hrasko'),
 ('Ruzena','Sipova'), 
 ('Ali','Baba'), 
 ('Jan','Polienko'), 
 ('Juraj','Trulo')  
--
SELECT * FROM ps.student
--zmena udajov v tabulke
UPDATE ps.student
 SET priezvisko='Lukova' WHERE priezvisko='Sipova'
--vymazanie poplozky
DELETE FROM ps.student
 WHERE priezvisko='Lukova'
--vlozenie polozky (rovnakej)
INSERT INTO ps.student
 VALUES
 ('Juraj','Trulo')
--vym. J. T. - zaznamy zatial boli nerozlisitelne - potrebujeme dodat nejaky identifikator
DELETE FROM ps.student
 WHERE priezvisko='Trulo'
--vym. tabulky----
DROP TABLE ps.student
--vytvorenie rozsirenej tabulky s ID
CREATE TABLE ps.student
 ( id          INTEGER,
   meno        VARCHAR(10),
   priezvisko  VARCHAR(20),
   datumnar     DATE,
   mesto       VARCHAR(20)
 )
--
INSERT INTO ps.student
 VALUES
 (1,'Jan','Hrasko','3.7.1980','Kosice'),
 (2,'Ruzena','Sipova','23.8.1983','Bardejov'), 
 (3,'Ali','Baba','27.2.1981','Bagdad'), 
 (4,'Jan','Polienko','1.4.1982','Kosice'), 
 (5,'Juraj','Trulo','1.4.1982','KOSICE'),  
 (6,'Juraj','Trulo','22.2.1922','Trebisov')  
--viac prikazov naraz sa oddeluje bodkociarkou
DELETE FROM ps.student
 WHERE id=6  -- J. T. isiel do pulb fiction
--vlozenie udajov v inom poradi ako stlpce tabulky predtym uvedene 
INSERT INTO ps.student(meno,priezvisko,mesto,daumnar,id)
 VALUES
  ('Juraj','Trulo','Trebisov','22.2.1922',6)  
--pocet ...
SELECT COUNT(*) FROM ps.student WHERE mesto='KOSICE'
--del.
--vytvorenie 2 tabuliek
CREATE TABLE ps.student
 ( id          INTEGER,
   meno        VARCHAR(10),
   priezvisko  VARCHAR(20),
   datumnar     DATE,
   id_mesto    INTEGER 
 );
CREATE TABLE ps.mesto
 ( id          INTEGER,
   nazov       VARCHAR(20)
 )
--2. tab. miest
INSERT INTO ps.mesto
 VALUES
 (21,'Kosice'),
 (22,'Bardejov'), 
 (23,'Bagdad'), 
 (24,'Trebisov')  
--1. tab. st.
INSERT INTO ps.student
 VALUES
 (1,'Jan','Hrasko','3.7.1980',21),
 (2,'Ruzena','Sipova','23.8.1983',22), 
 (3,'Ali','Baba','27.2.1981',23), 
 (4,'Jan','Polienko','1.4.1982',21), 
 (5,'Juraj','Trulo','1.4.1982',21),  
 (6,'Juraj','Trulo','22.2.1922',24)

-----------------------
Cviko 3
-----------------------
SELECT * FROM ps.student, ps.mesto --- vypise obe tabulky (24 zaznamov (6*4)) 
--aby vypisal iba 6 zaznamov
SELECT * FROM ps.student, ps.mesto 
  WHERE ps.student.id_mesto=ps.mesto.id -- tabulky su spojene cez stlpce
--problem je v tom, ze id je tu aj tu

--vypise tabulku 1, kde ps.student.id_mesto=ps.mesto.id, 
pricom stlpce id_mesto z ps.student a id z ps.mesto nie su vypisane
SELECT ps.student.id, meno, priezvisko, datumnar, nazov
FROM ps.student, ps.mesto 
  WHERE ps.student.id_mesto=ps.mesto.id

--alias tabulky
SELECT s.id, meno, priezvisko, datumnar, nazov
FROM ps.student AS s, ps.mesto AS m
  WHERE s.id_mesto=m.id
-- AS nie je nevyhnutne
--vypis vsetkych ludi z kosic
SELECT s.id, meno, priezvisko, datumnar, nazov
FROM ps.student s, ps.mesto m
  WHERE s.id_mesto=m.id and nazov='Kosice' -- alebo m.id=21
--kolko ich je? - 3
SELECT COUNT(*)
FROM ps.student s, ps.mesto m
  WHERE s.id_mesto=m.id and m.nazov='Kosice'

--mesto a pocet vsetkych, ktory su z neho
SELECT nazov, COUNT(*)
  FROM ps.student s, ps.mesto m
  WHERE s.id_mesto=m.id
  GROUP BY m.nazov
--kde pocet studentov je aspon 2
SELECT nazov, COUNT(*) 
  FROM ps.student s, ps.mesto m
  WHERE s.id_mesto=m.id 
  GROUP BY m.nazov
  HAVING COUNT(*)>1
--vypise iba KE s 3
SELECT nazov, COUNT(*)AS pocet --alias AS pocet je na ...nic(zbytocny, co sa tyka HAVINGu)
  FROM ps.student s, ps.mesto m
  WHERE s.id_mesto=m.id 
  GROUP BY m.nazov
  HAVING COUNT(*)>1

--vypis vsetkych ludi medzi narodenych v den
SELECT s.id, meno, priezvisko, datumnar, nazov 
  FROM ps.student s, ps.mesto m
  WHERE s.id_mesto=m.id and datumnar='1.4.1982'
-----DATE('1.4.1982') alebo ak nevieme v akom formate je datum
     zadany DATE('1.4.1982') - konverzia datumu
--dvomi datumami
SELECT s.id, meno, priezvisko, datumnar, nazov 
  FROM ps.student s, ps.mesto m
  WHERE s.id_mesto=m.id and datumnar>='1.4.1981' and datumnar<='1.4.1999'
-----alebo WHERE datumnar BETWEEN '   ' and '    '
-----funguje aj pre mena

--vypis meno, priezvisko a datum narodenia
SELECT meno, priezvisko, YEAR(datumnar) 
  FROM ps.student s
----YEAR(datum) je taka pekna fcia, ktora z datumu vypluje rok

--vypis rok, pocet ludi, ktory sa v nom narodili 
SELECT YEAR(datumnar), COUNT(*) 
  FROM ps.student s
  GROUP BY YEAR(datumnar)

--------------------------------------
CVIKO 4
--------------------------------------
- vloz. do tab este raz J. Hrasko
- -||- mesto KE
- pozri cv. 3
- vypise 8 ludi z KE, cisty bordel
- vytvorenie tabulky s prim. key
CREATE TABLE ps.student
 ( id          INTEGER NOT NULL,
   meno        VARCHAR(10),
   priezvisko  VARCHAR(20),
   datumnar     DATE,
   id_mesto    INTEGER, 
   PRIMARY KEY(id)
 )
---
CREATE TABLE ps.mesto
 ( id          INTEGER NOT NULL,
   nazov       VARCHAR(20),
   PRIMARY KEY(id)
 )
- vyplnenie udajmi
INSERT INTO ps.mesto
 VALUES
 (21,'Kosice'),
 (22,'Bardejov'), 
 (23,'Bagdad'), 
 (24,'Trebisov')  
;
 INSERT INTO ps.student
 VALUES
 (1,'Jan','Hrasko','3.7.1980',21),
 (2,'Ruzena','Sipova','23.8.1983',22), 
 (3,'Ali','Baba','27.2.1981',23), 
 (4,'Jan','Polienko','1.4.1982',21), 
 (5,'Juraj','Trulo','1.4.1982',21),  
 (6,'Juraj','Trulo','22.2.1922',24)

-- vloz. udajov, bez toho, aby v ps.mesto bolo mesto s 
   takym id
INSERT INTO ps.student
 VALUES
 (7,'Martin','Klingac','13.7.1980',26)
-- da sa to preto, lebo my spajame tab.
   iba teoreticky
-- dalsia krepost --> 
DELETE FROM ps.mesto
 WHERE nazov='Kosice'
-- ostane redukovany pocet zaznamov
-- vycistenie zaznamov
DELETE FROM ps.mesto
DELETE FROM ps.student
-- overenie
SELECT * FROM ps.student
SELECT * FROM ps.mesto

---vytvorenie prepojenia medzi prazdnymi tabulkami
ALTER TABLE ps.student
 ADD FOREIGN KEY sm(id_mesto) REFERENCES ps.mesto
-- najprv treba udajmi naplnit tab. miest a potom tab. studentov
   inak ohlasi chybu - something o parents
*******
SELECT ps.student.id, meno, priezvisko, datumnar, nazov
FROM ps.student, ps.mesto 
  WHERE ps.student.id_mesto=ps.mesto.id
*******
INSERT INTO ps.mesto
 VALUES
 (25,'Presov')

INSERT INTO ps.student
 VALUES
 (7,'Martin','Klingac','13.7.1980',26)

DELETE FROM ps.student
 WHERE priezvisko='Klingac'

DELETE FROM ps.mesto
 WHERE nazov='Kosice'
-- neda sa 
DELETE FROM ps.mesto
 WHERE nazov='Presov'
-- da sa vymazat
---> pridanie stlpca k tabulke
ALTER TABLE ps.student
 ADD COLUMN izba CHAR(5)
-- vyplnenie stlpceka
UPDATE ps.student
 SET izba='120A' WHERE ps.student.id=1
-- postupne pre vsetkych
*******
SELECT ps.student.id, meno, priezvisko, datumnar, nazov, izba
FROM ps.student, ps.mesto 
  WHERE ps.student.id_mesto=ps.mesto.id
*******
- vypis vsetkych, ktory byvaju na internate
SELECT ps.student.id, meno, priezvisko, datumnar, nazov, izba
FROM ps.student, ps.mesto 
  WHERE ps.student.id_mesto=ps.mesto.id
        AND izba IS NOT NULL ---> alebo NOT(izba IS NULL)

SELECT ps.student.id, meno, priezvisko, datumnar, nazov, izba
FROM ps.student, ps.mesto 
  WHERE ps.student.id_mesto=ps.mesto.id
        AND izba IS NULL

-- vypis spolubyvajucich - este nedokonaly
SELECT a.izba, a.meno , a.priezvisko, b.meno, b.priezvisko
  FROM ps.student a, ps.student b
  WHERE a.izba=b.izba

-------------------------------------
CVIKO 5******************************
-------------------------------------
najprv. zrusim foreign key -
ALTER TABLE ps.student DROP CONSTRAINT sm - alebo namiesto
	constraint - FOREIGN KEY

- 2 sposoby vazieb medzo tabulkami - 1. ako minule cv

	2. ALTER TABLE ps.student ADD FOREIGN KEY sm(id_mesto)
	 REFERENCES ps.mesto ON DELETE CASCADE
- ked sa zmaze z ps.mesto, tak sa vymazu aj polozky z 
ps.student, ktore suvisia so zmazanou
- ak na pisem ON RESTRICT CASCADE - bude to iste ako minule 
  cviko

- vymazanie Kosic
DELETE FROM ps.mesto
 WHERE nazov='Kosice'
- stalo sa, ze kosicania su papaaaa
*******
SELECT ps.student.id, meno, priezvisko, datumnar, nazov, izba
FROM ps.student, ps.mesto 
  WHERE ps.student.id_mesto=ps.mesto.id
*******
- vypis spolubyvajucich
SELECT *
  FROM ps.student a, ps.student b
  WHERE a.izba=b.izba and a.id<b.id

INSERT INTO ps.student
 VALUES
 (4,'Jan','Polienko','1.4.1982',21,'220B'), 
 (5,'Juraj','Trulo','1.4.1982',21,'220B')

- vypis vsetkych spolubyvajucich J. Hraska
SELECT *
  FROM ps.student a, ps.student b
  WHERE a.izba=b.izba and a.id<>b.id
  AND a.priezvisko='Hrasko'

- pridame tabulky skupiny, typ izby...
pozri schemu v poznamkach z cvika
- vytvaranie tabuliek ...
CREATE TABLE ps.mesto
( 
  id INTEGER NOT NULL,
  nazov VARCHAR(20) NOT NULL,
  PRIMARY KEY(id)
)

CREATE TABLE ps.skupina
(
  id INTEGER NOT NULL,
  nazov VARCHAR(20) NOT NULL,
  kod VARCHAR(5) NOT NULL,
  PRIMARY KEY(id)
)

CREATE TABLE ps.typ_izby
(
  id INTEGER NOT NULL,
  kod VARCHAR(5) NOT NULL,
  popis VARCHAR(20) NOT NULL,
  kapacita INTEGER NOT NULL,
  poplatok DECIMAL(5,2) NOT NULL, 
  PRIMARY KEY(id)
)

CREATE TABLE ps.izba
(
  id INTEGER NOT NULL,
  cislo VARCHAR(5) NOT NULL,
  id_typ INTEGER NOT NULL,
  PRIMARY KEY(id)
)

CREATE TABLE ps.student
(
  id INTEGER NOT NULL,
  meno VARCHAR(10) NOT NULL,
  priezvisko VARCHAR(20) NOT NULL,
  datum_nar DATE NOT NULL,
  pohlavie CHAR(1) NOT NULL,
  rocnik INTEGER NOT NULL,
  id_mesto INTEGER NOT NULL,
  id_izba INTEGER,
  id_skupina INTEGER NOT NULL,
  PRIMARY KEY(id)
)

- vytvaranie vazieb
ALTER TABLE ps.student
 ADD FOREIGN KEY sm(id_mesto) REFERENCES ps.mesto

ALTER TABLE ps.student
 ADD FOREIGN KEY ss(id_skupina) REFERENCES ps.skupina

ALTER TABLE ps.student
 ADD FOREIGN KEY si(id_izba) REFERENCES ps.izba

ALTER TABLE ps.izba
 ADD FOREIGN KEY it(id_typ) REFERENCES ps.typ_izby

- naplnenie tabuliek - najprv tie, na ktore sa viazu ine
- este nevlozene
INSERT INTO ps.mesto
 VALUES
 (1,'Kosice'),
 (2,'Bardejov'), 
 (3,'Bagdad'), 
 (4,'Trebisov') 

INSERT INTO ps.skupina
 VALUES
 (1,'Matematika','M'),
 (2,'Informatika','I'), 
 (3,'Matematika-Fyzika','MF') 
 
INSERT INTO ps.typ_izby
 VALUES
 (1,'M6A','2 os. bez TV',2,500.00),
 (2,'M6B','3 bez pristelku',3,450.00)

-----------------------------------
CVIKO 6   *************************
-----------------------------------
- spustenie db2 cez command center v ponuke start
CONNECT TO vyuka USER vyuka USING vyuka

- vyplnanie dat do tabuliek
INSERT INTO ps.izba
 VALUES
 (1,'120A',1),
 (2,'220B',2)

INSERT INTO ps.student
 VALUES
 (1,'Jan','Hrasko','12.12.1888','M',2,1,1,1),
 (2,'Ruzena','Sipova','11.11.1777','F',3,2,NULL,3), 
 (3,'Ali','Baba','31.7.1981','M',1,3,2,2), 
 (4,'Jan','Polienko','24.12.1986','M',2,1,2,3), 
 (5,'Juraj','Trulo','1.4.1492','M',3,1,2,1),  
 (6,'Juraj','Trulo','2.4.1492','M',3,4,1,2)

- kontrola naplnenia tab.
SELECT * FROM ps.student
SELECT * FROM ps.mesto
SELECT * FROM ps.skupina
SELECT * FROM ps.izba
SELECT * FROM ps.typ_izby

- suhrny select
SELECT a.id, a.meno, a.priezvisko, a.datum_nar,a.pohlavie,a.rocnik,b.nazov,c.nazov,d.kod,d.popis,d.kapacita,d.poplatok,e.cislo,e.
 FROM ps.student a,ps.mesto b,ps.skupina c,ps.izba d, ps.typ_izby e
 WHERE a.id_mesto=b.id and a.id_izba=d.id and a.id_skupina=c.id AND d.id_typ=e.id

- inak 
SELECT *
FROM ps.student s
  JOIN ps.mesto m ON s.id_mesto=m.id
  JOIN ps.skupina g ON s.id_skupina=g.id
  JOIN ps.izba i ON s.id_izba=i.id
  JOIN ps.typ_izby t ON i.id_typ=t.id
  
- matikov   
SELECT *
FROM ps.student s
  JOIN ps.mesto m ON s.id_mesto=m.id
  JOIN ps.skupina g ON s.id_skupina=g.id
  JOIN ps.izba i ON s.id_izba=i.id
  JOIN ps.typ_izby t ON i.id_typ=t.id  
 WHERE g.kod='M'
-- vypise iba 2
- matika alebo inf.
SELECT *
FROM ps.student s
  JOIN ps.mesto m ON s.id_mesto=m.id
  JOIN ps.skupina g ON s.id_skupina=g.id
  JOIN ps.izba i ON s.id_izba=i.id
  JOIN ps.typ_izby t ON i.id_typ=t.id  
 WHERE g.kod='M' OR g.kod='I'
-- alebo g.kod IN ('M','I') - vypise 4
-- ale stale nevypisuje Ruzenku

- student a jeho pohlavie
SELECT meno, priezvisko, pohlavie 
 FROM ps.student  
- teraz aby namiesto M a F bolo napisane muz, zena
SELECT meno, priezvisko, (CASE pohlavie 
                           WHEN 'M' THEN 'muz'
			   WHEN 'F' THEN 'zena'
			 END)
 FROM ps.student
- student, cislo izby pomocou joinu
SELECT s.meno, s.priezvisko, i.cislo
FROM ps.student s
  JOIN ps.izba i ON s.id_izba=i.id
-- chuda Ruzenka tu zas nie je
--- chceme aj Ruzenku do zoznamu ako na to?
--- nenapiseme JOIN ale LEFT OUTER JOIN
SELECT s.meno, s.priezvisko, i.cislo
FROM ps.student s
  LEFT OUTER JOIN ps.izba i ON s.id_izba=i.id

- navrat k selectu zo zaciatku
SELECT *
FROM ps.student s
  JOIN ps.mesto m ON s.id_mesto=m.id
  JOIN ps.skupina g ON s.id_skupina=g.id
  LEFT OUTER JOIN ps.izba i ON s.id_izba=i.id
  LEFT OUTER JOIN ps.typ_izby t ON i.id_typ=t.id

- student a jeho poplatok za izbu

-----------------------------------
CVIKO 7 ***************************
-----------------------------------
INSERT INTO ps.typ_izby
 VALUES
 (3,'Lux','Apartman',1,1000)
- 1000 nezobral - 999 preslo - pozri vytvaranie tab...

INSERT INTO ps.izba
 VALUES
 (3,'320A',3)

- student a izba, na kt. byva
SELECT s.priezvisko, s.meno, i.cislo
FROM ps.student s
  LEFT OUTER JOIN ps.izba i ON s.id_izba=i.id
- a co tak RIGHT OUTER JOIN
SELECT s.priezvisko, s.meno, i.cislo
FROM ps.student s
  RIGHT OUTER JOIN ps.izba i ON s.id_izba=i.id
- nevypise Ruzenku, ale vypise izbu, ktora nie je obyvana 320A

SELECT s.priezvisko, s.meno, i.cislo
FROM ps.student s
  FULL OUTER JOIN ps.izba i ON s.id_izba=i.id
- vypise Ruzenku, aj neobyvanu izbu
- mame teda 4 rozdielne JOINy - (INNER sa nepise) JOIN, 
  LEFT OUTER JOIN - za zakl. berieme 1. tab 
  RIGHT OUTER JOIN - za zakl. berieme 2. tab
  FULL OUTER JOIN - komb. left a right

- vypis studenta a jeho poplatku za izbu
SELECT s.priezvisko, s.meno, t.poplatok 
 FROM ps.student s
  LEFT OUTER JOIN ps.izba i ON s.id_izba=i.id
  LEFT OUTER JOIN ps.typ_izby t ON i.id_typ=t.id

PRIEZVISKO           MENO       POPLATOK
-------------------- ---------- --------
Hrasko               Jan          500,00
Trulo                Juraj        500,00
Baba                 Ali          450,00
Polienko             Jan          450,00
Trulo                Juraj        450,00
Sipova               Ruzena            -  ---> aby naniesto pomlcky bola 0
						COALESCE(.,.,.,...,.) - prva nenullova hodnota
--------------------------- Command entered ----------------------------
SELECT s.priezvisko, s.meno, COALESCE(t.poplatok,0) 
 FROM ps.student s
  LEFT OUTER JOIN ps.izba i ON s.id_izba=i.id
  LEFT OUTER JOIN ps.typ_izby t ON i.id_typ=t.id

------------------------------------------------------------------------

PRIEZVISKO           MENO       3              
-------------------- ---------- ---------------
Hrasko               Jan                 500,00
Trulo                Juraj               500,00
Baba                 Ali                 450,00
Polienko             Jan                 450,00
Trulo                Juraj               450,00
Sipova               Ruzena                0,00

- iba internatnici
SELECT s.priezvisko, s.meno, t.poplatok 
 FROM ps.student s
  JOIN ps.izba i ON s.id_izba=i.id
  JOIN ps.typ_izby t ON i.id_typ=t.id

PRIEZVISKO           MENO       POPLATOK
-------------------- ---------- --------
Hrasko               Jan          500,00
Trulo                Juraj        500,00
Baba                 Ali          450,00
Polienko             Jan          450,00
Trulo                Juraj        450,00

--------------------------- Command entered ----------------------------
SELECT s.priezvisko, s.meno, t.poplatok 
 FROM ps.student s
  FULL JOIN ps.izba i ON s.id_izba=i.id
  FULL JOIN ps.typ_izby t ON i.id_typ=t.id

------------------------------------------------------------------------

PRIEZVISKO           MENO       POPLATOK
-------------------- ---------- --------
Hrasko               Jan          500,00
Trulo                Juraj        500,00
Baba                 Ali          450,00
Polienko             Jan          450,00
Trulo                Juraj        450,00
-                    -            999,00
Sipova               Ruzena            -

- typ izby a suma poplatkov za danu izbu
--------------------------- Command entered ----------------------------
SELECT t.popis, SUM(t.poplatok) 
FROM ps.student s
  JOIN ps.izba i ON s.id_izba=i.id
  JOIN ps.typ_izby t ON i.id_typ=t.id
 GROUP BY t.popis
------------------------------------------------------------------------

POPIS                2                                
-------------------- ---------------------------------
2 os. bez TV                                   1000,00
3 bez pristelku                                1350,00

- dodajme apartman
--------------------------- Command entered ----------------------------
SELECT t.popis, SUM(t.poplatok) 
FROM ps.student s
  RIGHT OUTER JOIN ps.izba i ON s.id_izba=i.id
  RIGHT OUTER JOIN ps.typ_izby t ON i.id_typ=t.id
 GROUP BY t.popis
------------------------------------------------------------------------

POPIS                2                                
-------------------- ---------------------------------
2 os. bez TV                                   1000,00
3 bez pristelku                                1350,00
Apartman                                        999,00 - tu nema co robit - este sa k tomu vratime

--------------------------- Command entered ----------------------------
SELECT t.popis, t.poplatok
FROM ps.student s
  RIGHT OUTER JOIN ps.izba i ON s.id_izba=i.id
  RIGHT OUTER JOIN ps.typ_izby t ON i.id_typ=t.id

------------------------------------------------------------------------

POPIS                POPLATOK
-------------------- --------
2 os. bez TV           500,00
2 os. bez TV           500,00
3 bez pristelku        450,00
3 bez pristelku        450,00
3 bez pristelku        450,00
Apartman               999,00

--------------------------- Command entered ----------------------------
SELECT *
FROM ps.student s
  RIGHT OUTER JOIN ps.izba i ON s.id_izba=i.id
  RIGHT OUTER JOIN ps.typ_izby t ON i.id_typ=t.id

------------------------------------------------------------------------

ID          MENO       PRIEZVISKO           DATUM_NAR  POHLAVIE ROCNIK      ID_MESTO    ID_IZBA     ID_SKUPINA  ID          CISLO ID_TYP      ID          KOD   POPIS                KAPACITA    POPLATOK
----------- ---------- -------------------- ---------- -------- ----------- ----------- ----------- ----------- ----------- ----- ----------- ----------- ----- -------------------- ----------- --------
          1 Jan        Hrasko               1888-12-12 M                  2           1           1           1           1 120A            1           1 M6A   2 os. bez TV                   2   500,00
          6 Juraj      Trulo                1492-04-02 M                  3           4           1           2           1 120A            1           1 M6A   2 os. bez TV                   2   500,00
          3 Ali        Baba                 1981-07-31 M                  1           3           2           2           2 220B            2           2 M6B   3 bez pristelku                3   450,00
          4 Jan        Polienko             1986-12-24 M                  2           1           2           3           2 220B            2           2 M6B   3 bez pristelku                3   450,00
          5 Juraj      Trulo                1492-04-01 M                  3           1           2           1           2 220B            2           2 M6B   3 bez pristelku                3   450,00
          - -          -                    -          -                  -           -           -           -           3 320A            3           3 Lux   Apartman                       1   999,00

- najvyssi poplatok
--------------------------- Command entered ----------------------------
SELECT max(poplatok)
FROM ps.typ_izby

------------------------------------------------------------------------

1      
-------
 999,00
- rozdiel medzi max a min poplatok
--------------------------- Command entered ----------------------------
SELECT max(poplatok) - min(poplatok)
FROM ps.typ_izby

------------------------------------------------------------------------

1       
--------
  549,00
--> studenti, ktory platia najnizsi poplatok - treba pouzit tzv. subselect 
--------------------------- Command entered ----------------------------
SELECT s.priezvisko
FROM ps.student s
  JOIN ps.izba i ON s.id_izba=i.id
  JOIN ps.typ_izby t ON i.id_typ=t.id
  WHERE poplatok=(SELECT MIN(poplatok) FROM ps.typ_izby)
------------------------------------------------------------------------

PRIEZVISKO          
--------------------
Baba                
Polienko            
Trulo               

- alebo inak
--------------------------- Command entered ----------------------------
SELECT s.priezvisko, t.poplatok
FROM ps.student s
  JOIN ps.izba i ON s.id_izba=i.id
  JOIN ps.typ_izby t ON i.id_typ=t.id
  WHERE t.poplatok<=ALL(SELECT MIN(poplatok) FROM ps.typ_izby)
------------------------------------------------------------------------

PRIEZVISKO           POPLATOK
-------------------- --------
Baba                   450,00
Polienko               450,00
Trulo                  450,00

- studenti, ktory platia max poplatok
--------------------------- Command entered ----------------------------
SELECT s.priezvisko, t.poplatok
FROM ps.student s
  JOIN ps.izba i ON s.id_izba=i.id
  JOIN ps.typ_izby t ON i.id_typ=t.id
  WHERE t.poplatok=ALL(SELECT MAX(poplatok) FROM ps.typ_izby)
------------------------------------------------------------------------

PRIEZVISKO           POPLATOK
-------------------- --------

  0 record(s) selected.